#profit 利润表
import xlwt

#字体样式
def get_fontStyle():
    font = xlwt.Font()
    font.name = '微软雅黑'
    font.height =  20 * 11
    return font

#背景样式
def getBackground():
    pattern = xlwt.Pattern()
    #背景类别
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
    pattern.pattern_fore_colour = 49  # 蓝色
    return pattern

#对齐样式
def getAlign():
    # 设置创建对齐
    alignment = xlwt.Alignment()
    # 设置对齐方式
    # 0x01(左端对齐)、0x02(水平方向上居中对齐)、0x03(右端对齐)
    alignment.horz = 0x02
    # 0x00(上端对齐)、 0x01(垂直方向上居中对齐)、0x02(底端对齐)
    alignment.vert = 0x01
    return alignment

#单元格宽度
def settingWidth(widthArray,wLength):
    for i in range(wLength):
        # 循环定义col变量名
        b = 'col_' + str(i)
        exec(b + '=sheet.col(%s)' % i)
        c = b + '.width'
        exec(c + '=256 * widthArray[%s] ' % i)

# 循环背景
#rowIndex 行数
def cirColor(wLength,alignStyle,rowIndex):
    for i in range(wLength):
        sheet.write(rowIndex, i, u' ', backgroundStyle)
    #合并第rowIndex行到第rowIndex行，第0列到第12列     ！可能会有bug！
    rowIndex+=1
    sheet.write_merge(rowIndex, rowIndex, 0, 12, '2021/10/19成都单品项盈亏', alignStyle)
    rowIndex += 1
    return rowIndex

#数据表格
def tableTh(sheet,alignStyle,wLength,rowIndex,rowCir):
    text=["客户","商品名称","单盒总成本","销售单价","销售扣点","应收","总部费用 + 税点","人工费","单盒盈亏","发货数量","总盈亏","平均损耗率","其他备注"]
    rowCir+=2
    flag=0
    for i in range(rowCir):
        if i == 0:
            for j in range(wLength):
                sheet.write(rowIndex, j,text[j], alignStyle)
        else:
            for j in range(wLength):
                if j == 4:
                    sheet.write(rowIndex, j,u'5%', alignStyle)
                elif j == 5:
                    sheet.write(rowIndex, j, xlwt.Formula(f'D{rowIndex+1} * (1 - E{rowIndex+1})'), alignStyle)
                elif j == 6:
                    sheet.write(rowIndex, j, u'2%', alignStyle)
                elif j == 8:
                    sheet.write(rowIndex, j, xlwt.Formula(f'F{rowIndex+1}-C{rowIndex+1}-G{rowIndex+1}*D{rowIndex+1}'), alignStyle)
                elif j == 10:
                    sheet.write(rowIndex, j, xlwt.Formula(f'I{rowIndex+1}*{rowIndex+1}'), alignStyle)
                else:
                    sheet.write(rowIndex, j, u'占位', alignStyle)
        rowIndex += 1
    #空一行
    rowIndex+=1
    return rowIndex

#批处理
#sheet 当前页
#aliginStyle 字体样式及对齐
#wLength 列数
#rowIndex 行数
#rowCir 循环行数
def repetition(sheet,alignStyle,wLength,rowIndex,rowCir):
    # 颜色初始化
    rowIndex=cirColor(wLength, alignStyle, rowIndex)
    #print(rowIndex)
    # 表头行
    rowIndex=tableTh(sheet, alignStyle, wLength,rowIndex,rowCir)

    return rowIndex

if __name__ == '__main__':

    #创建表文件
    table = xlwt.Workbook(encoding='utf-8')
    #创建页
    sheet = table.add_sheet("sheet1")
    #行定位
    rowIndex=0
    #表格宽度初始化
    widthArray = [21, 30, 12, 12, 10, 11, 18, 10, 11, 10, 16, 14, 35]
    wLength=len(widthArray)
    settingWidth(widthArray,wLength)

    #背景和对齐方式初始化
    #文字装载
    backgroundStyle=xlwt.XFStyle()
    backgroundStyle.pattern=getBackground()
    alignStyle=xlwt.XFStyle()
    alignStyle.alignment=getAlign()
    alignStyle.font = get_fontStyle()

    #循环行数
    rowCir=5
    for i in range(rowCir):
        rowIndex=repetition(sheet,alignStyle,wLength,rowIndex,rowCir)

    table.save('resouce/demo.xls')